﻿using System;
using System.Linq;
using System.Collections.ObjectModel;
using MySql.Data.MySqlClient;
using Reviewer.Data.Bases;
using Reviewer.Entities;
using Reviewer.Utilities.Data;
using System.Globalization;
using System.Data;

namespace Reviewer.Data
{
    /// <summary>
    /// Game views repository
    /// </summary>
    [CLSCompliant(false)]
    public class GameViewRepository : Bases.RepositoryCrudBase<GameView, Int32>
    {
        #region fields (private)
        private const string TABLE_NAME = "V2_SpilViews";
        private static readonly CrudColumnInfo columnId = new CrudColumnInfo(0, "ID", MySqlDbType.Int32, true, false, false, true);
        private static readonly CrudColumnInfo columnGameId = new CrudColumnInfo(1, "SpilId", MySqlDbType.Int32, false, true, false);
        private static readonly CrudColumnInfo columnViews = new CrudColumnInfo(2, "Views", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnViewDate = new CrudColumnInfo(3, "Dato", MySqlDbType.DateTime, false, false, true);

        #endregion

        public Collection<GameView> SelectTopByDate(DateTime date, int topCount)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = String.Format(CultureInfo.InvariantCulture,
                    "SELECT GV.*, G.Titel AS GameTitle, G.SpilId AS GameGameId, G.Format AS GameFormat, F.Navn AS GameFormatName "
                    + "FROM {0} GV LEFT OUTER JOIN V2_Spil G ON (GV.SpilID = G.ID) LEFT OUTER JOIN V2_GloFormat F ON (G.Format = F.Id)"
                    + "WHERE CAST(GV.Dato AS DateTime) >= @Date ORDER BY GV.Views DESC LIMIT {1}"
                    , GameViewRepository.TABLE_NAME
                    , topCount);
                command.Parameters.AddWithValue("@Date", date.Date);
                return this.ExecuteReaderAndBuildList(command);
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1720:IdentifiersShouldNotContainTypeNames", MessageId = "guid", Justification = "Name is GameGuid")]
        public void AddGameView(int gameId, Guid gameGuid)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = "INSERT INTO GameViews (GameId, GameUid, ViewDate) VALUES (@GameId, @GameUid, @ViewDate) ON DUPLICATE KEY UPDATE Views=Views+1";
                command.Parameters.AddWithValue("@GameId", gameId);
                command.Parameters.AddWithValue("@GameUid", gameGuid);
                command.Parameters.AddWithValue("@ViewDate", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, 0, 0));
                this.ExecuteNonQuery(command);

            }
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = String.Format(CultureInfo.InvariantCulture,
                    "UPDATE {0} SET {1} = {1} + 1 WHERE {2} = @{2} AND {3} = @{3}"
                    , GameViewRepository.TABLE_NAME
                    , GameViewRepository.columnViews.Name
                    , GameViewRepository.columnGameId.Name
                    , GameViewRepository.columnViewDate.Name);
                GameViewRepository.columnGameId.AddParameter(command, gameId);
                GameViewRepository.columnViewDate.AddParameter(command, DateTime.Today);

                if (this.ExecuteNonQuery(command) == 0)
                {
                    GameView item = new GameView();
                    item.GameId = gameId;
                    item.ViewDate = DateTime.Today;
                    item.Views = 1;
                    this.Create(item);
                }
            }
        }

        public DataSet ViewStatisticsByGenre()
        {
            return null;
        }

        public DataSet ViewStatisticsByFormat()
        {
            return null;
        }

        public DataSet GetViewStatistics(Int32? formatId, Int32? genreId)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.CommandText = "SELECT gw.GameId, gw.GameUID, g.Titel as Title, g.Format as FormatId,"
+ " IFNULL(SUM(IF(gw.ViewDate >= DATE(NOW()),gw.Views,0)),0) AS ViewsToday,"
+ " IFNULL(SUM(IF(DATE(gw.ViewDate) = (DATE(NOW()) - INTERVAL 1 DAY),gw.Views,0)),0) AS ViewsYesterday,"
+ " IFNULL((SELECT SUM(gwi.Views) FROM GameViews gwi WHERE DATE(gwi.ViewDate) = DATE(NOW())),0) AS TotalViewsToday,"
+ " IFNULL((SELECT SUM(gwi.Views) FROM GameViews gwi WHERE DATE(gwi.ViewDate) = (DATE(NOW())-INTERVAL 1 DAY)),0) AS TotalViewsYesterday"
+ " FROM GameViews gw"
+ " LEFT OUTER JOIN V2_Spil g ON (gw.GameId = g.Id)"
+ " GROUP BY gw.GameId, gw.GameUID"
+ " ORDER BY ViewsToday DESC, ViewsYesterday DESC"
+ " LIMIT 10";
                return base.FillDataSet(cmd);
            }
        }

        #region save
        //        public DataSet GameViewStatistics()
        //        {
        //            using (MySqlCommand cmd = new MySqlCommand())
        //            {
        //                select GameUID, g.Titel, g.Format,
        //                SUM(IF(ViewDate >= CAST(Now() AS DATETIME) AND ViewDate < CAST(Now() AS DATE) + INTERVAL 1 DAY,Views,0)) AS SumViewsToday,
        //                SUM(IF(ViewDate >= CAST(Now() AS DATETIME) - INTERVAL 8 DAY AND ViewDate <= CAST(Now() AS DATETIME) - INTERVAL 1 DAY,Views,0)) AS SumViewsWeek,
        //                SUM(IF(ViewDate < CAST(Now() AS DATE),Views,0)) AS SumTotal
        //                from gameviews gw
        //                left outer join V2_Spil g on (gw.gameid = g.id)

        ///*group by gameid, gameuid*/
        ///*group by gameuid, g.format, g.genre*/
        //group by g.genre, gameuid

        //order by sumviewstoday desc
        //                cmd.CommandText = "";
        //                cmd.Parameters.Add();
        //                base.FillDataSet("";);
        //            }
        //        }
        #endregion

        public DataSet GetViews()
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText =
@"SELECT VV.VD AS ViewDate, VV.GameUid AS GameId, VV.Views,(VV.Views / VT.TotalViews) * 100 AS Percentage,S.Title,(VV.Views / VT.TotalViews) * 100 AS Percentage, S.FormatName
FROM (
    SELECT Date(ViewDate) AS VD, GameUid, SUM(Views) AS Views
    FROM GameViews
    WHERE DATEDIFF(now(),ViewDate) = 0
    GROUP BY Date(ViewDate), GameUid
    ORDER BY Date(ViewDate) DESC, SUM(Views) DESC
    LIMIT 10
) AS VV
LEFT OUTER JOIN
(
    SELECT DATE(ViewDate) AS TotalDate, SUM(Views) AS TotalViews
    FROM GameViews
    GROUP BY DATE(ViewDate)
    ORDER BY DATE(ViewDate) DESC
    LIMIT 3
) AS VT ON (VV.VD = VT.TotalDate)
LEFT OUTER JOIN
(
  SELECT SS.SpilId, SS.Titel AS Title, SS.Format, SSF.Navn AS FormatName
  FROM V2_Spil SS
  LEFT OUTER JOIN V2_GloFormat SSF ON SS.Format = SSF.Id
  GROUP BY SS.SpilId
) S ON (VV.GameUID = S.SpilId COLLATE 'latin1_danish_ci')
ORDER BY Percentage DESC;
";

                DataSet top10Set = base.FillDataSet(command);

                command.CommandText =
@"SELECT VV.VD AS ViewDate, VV.GameUid AS GameId, VV.Views,(VV.Views / VT.TotalViews) * 100 AS Percentage
FROM (
    SELECT Date(ViewDate) AS VD, GameUid, SUM(Views) AS Views
    FROM GameViews
    WHERE DATEDIFF(now(),ViewDate) = 1
    GROUP BY Date(ViewDate), GameUid
    ORDER BY Date(ViewDate) DESC, SUM(Views) DESC
) AS VV
LEFT OUTER JOIN
(
    SELECT DATE(ViewDate) AS TotalDate, SUM(Views) AS TotalViews
    FROM GameViews
    GROUP BY DATE(ViewDate)
    ORDER BY DATE(ViewDate) DESC
    LIMIT 3
) AS VT ON (VV.VD = VT.TotalDate)
ORDER BY Percentage DESC";

                DataSet yesterday = base.FillDataSet(command);

                top10Set.Tables[0].Columns.Add("Position");
                top10Set.Tables[0].Columns.Add("LastPosition");
                top10Set.Tables[0].Columns.Add("LastPercentage");
                foreach (DataRow row in top10Set.Tables[0].Rows)
                {
                    row["Position"] = top10Set.Tables[0].Rows.IndexOf(row) + 1;
                    DataRow[] foundRows = yesterday.Tables[0].Select("GameId='" + row["GameId"].ToString() + "'");
                    if (foundRows.Length > 0)
                    {
                        row["LastPercentage"] = foundRows[0]["Percentage"];
                        row["LastPosition"] = yesterday.Tables[0].Rows.IndexOf(foundRows[0]) + 1;
                    }
                    else
                    {
                        row["LastPercentage"] = 0;
                        row["LastPosition"] = -1;
                    }
                }

                return top10Set;
            }
        }

        #region methods (protected)
        internal override CrudColumnInfo[] Columns
        {
            get
            {
                return new CrudColumnInfo[]{
                    columnId
                    , columnGameId
                    , columnViews
                    , columnViewDate
                };
            }
        }

        protected override string TableName
        {
            get { return GameViewRepository.TABLE_NAME; }
        }

        internal override object ColumnToValue(CrudColumnInfo info, GameView item)
        {
            if (info.Equals(GameViewRepository.columnId)) return item.Id;
            else if (info.Equals(GameViewRepository.columnGameId)) return item.GameId;
            else if (info.Equals(GameViewRepository.columnViews)) return item.Views;
            else if (info.Equals(GameViewRepository.columnViewDate)) return item.ViewDate;
            return null;
        }

        protected override GameView BuildItemFromReader(System.Data.IDataReader reader)
        {
            GameView item = new GameView();
            if (reader != null)
            {
                item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameViewRepository.columnId.Name), 0);
                item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameViewRepository.columnGameId.Name), 0);
                item.Views = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameViewRepository.columnViews.Name), 0);
                item.ViewDate = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameViewRepository.columnViewDate.Name), DateTime.MinValue);

                int ordinal;
                if ((ordinal = reader.GetOrdinal("GameTitle")) > 0)
                {
                    item.GameTitle = DataReaderUtility.GetValue(reader, ordinal, String.Empty);
                }

                if ((ordinal = reader.GetOrdinal("GameGameId")) > 0)
                {
                    item.GameGameId = DataReaderUtility.GetValue(reader, ordinal, Guid.Empty);
                }

                if ((ordinal = reader.GetOrdinal("GameFormat")) > 0)
                {
                    item.GameFormat = DataReaderUtility.GetValue(reader, ordinal, 0);
                }

                if ((ordinal = reader.GetOrdinal("GameFormatName")) > 0)
                {
                    item.GameFormatName = DataReaderUtility.GetValue(reader, ordinal, String.Empty);
                }
            }
            return item;
        }
        #endregion
    }
}
